﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

namespace DoAnWeb
{
    class DataProvider
    {
        //Khai báo biến
        private string _strConnectionString = ConfigurationManager.ConnectionStrings["StrNhomMua"].ConnectionString;
        private SqlConnection _connection;
        private string _error = "";
        //Khai báo property
        public string ConnectionString
        {
            get { return _strConnectionString; }
            private set { _strConnectionString = value; }
        }
        public SqlConnection Connection
        {
            get { return _connection; }
            private set { _connection = value; }
        }
        public string Error
        {
            get { return _error; }
            private set { _error = value; }
        }
        //Tạo đối tượng kết nối
        public DataProvider() { }
        public DataProvider(string strConnection)
        {
            _strConnectionString = strConnection;
            _connection = new SqlConnection(_strConnectionString);
        }
        //Mở kết nối đến CSDL
        public bool OpenConnect()
        {
            try
            {
                if (_connection == null)
                    _connection = new SqlConnection(_strConnectionString);
                if (_connection.State == ConnectionState.Open)
                    _connection.Close();
                _connection.Open();
                return true;
            }
            catch (SqlException ex)
            {
                Error = ex.Message;
            }
            return false;
        }
        //Đóng kết nối
        public bool CloseConnect()
        {
            try
            {
                if (_connection != null)
                    if (_connection.State == ConnectionState.Open)
                        _connection.Close();
                return true;
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                return false;
            }
        }
        //Thực thi truy vấn với các lệnh insert, update, delete
        public bool SqlExcuteNonQuery(string procName, params SqlParameter[] sqlParameters)
        {
            try
            {
                if (OpenConnect())
                {
                    SqlCommand sqlCommand = _connection.CreateCommand();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = procName;
                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);
                    sqlCommand.ExecuteNonQuery();
                    Error = "";
                    return true;
                }
                return false;
            }
            catch (SqlException ex)
            {
                _error = ex.Message;
                return false;
            }
            finally
            {
                CloseConnect();
            }
        }
        //Thực thi truy vấn với lệnh Select
        public DataTable SqlExcuteQuery(string procName, params SqlParameter[] sqlParameters)
        {
            DataTable dt = null;
            try
            {
                if (OpenConnect())
                {
                    SqlCommand sqlCommand = _connection.CreateCommand();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = procName;
                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);
                    var dbadapter = new SqlDataAdapter(sqlCommand);
                    dt = new DataTable();
                    dbadapter.Fill(dt);
                    Error = "";
                }
            }
            catch (SqlException ex)
            {
                Error = ex.Message;
            }
            finally
            {
                CloseConnect();
            }
            return dt;
        }
        //Trả về 1 giá trị đầu tiên
        public object SqlExcuteScalar(string procName, params SqlParameter[] sqlParameters)
        {
            object value = null;
            try
            {
                if (OpenConnect())
                {
                    var sqlCommand = _connection.CreateCommand();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = procName;
                    if (sqlParameters != null)
                        sqlCommand.Parameters.AddRange(sqlParameters);
                    value = sqlCommand.ExecuteScalar();
                    Error = "";
                }
            }
            catch (SqlException ex)
            {
                Error = ex.Message;
            }
            finally
            {
                CloseConnect();
            }
            return value;
        }
    }
}
